package com.example.myapplication;


import android.os.Environment;
import android.util.Log;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * Created by YC_chen on 2017/6/21.
 */

public class ExcelUtils {

    public static boolean readSheet1(File file) throws IOException, BiffException {
        if (file.canRead()) {
            InputStream in = new FileInputStream(file);
            Workbook workbook = Workbook.getWorkbook(in);
            Sheet sheet = workbook.getSheet(0);
            List<Investigation> list = new ArrayList<>();
            for (int i = 1; i < sheet.getRows(); i++) {
                Investigation investigation = new Investigation();
                Cell[] cells = sheet.getRow(i);
                investigation.setPoint(cells[0].getContents());
                investigation.setPointName(cells[1].getContents());
                investigation.setAddress(cells[2].getContents());
                investigation.setAddressDetails(cells[3].getContents());
                investigation.setThirdIndex(cells[4].getContents());
                investigation.setStandard(cells[5].getContents());
                investigation.setCode(cells[6].getContents());
                investigation.setInvestigationStandard(cells[7].getContents());
                investigation.setTestWay(cells[8].getContents());
                investigation.setTestItem(cells[9].getContents());
                investigation.setTestResult(cells[10].getContents());
                investigation.setRecord(cells[11].getContents());
                investigation.setPicture(cells[12].getContents());
                investigation.setRandom(cells[13].getContents()
                );
                investigation.save();
            }
            return true;
        } else {
            return false;
        }
    }

    public static boolean writeExcel(String excelName, List<Investigation> list) throws IOException, WriteException {
        String info[] = {"考察点", "考察名称", "实地名称", "详细地址", "三级指标", "标准", "内码", "考察标准", "测评方式", "测评项", "测评结果", "现场记录", "照片"};
        File sd = Environment.getExternalStorageDirectory();
        String path = sd.getPath() + "/" + excelName;
        File dir = new File(path);
        if (!dir.exists()) {
            dir.mkdir();
        }
        File file = new File(dir, excelName + ".xls");
        if (!file.exists()) {
            file.createNewFile();
        }
        file.setWritable(true);
        OutputStream out = new FileOutputStream(file);
        WritableWorkbook workbook = Workbook.createWorkbook(out);
        WritableSheet sheet = workbook.createSheet(excelName, 0);
        for (int i = 0; i < 13; i++) {
            Label label = new Label(i, 0, info[i]);
            sheet.addCell(label);
        }
        for (int i = 0; i < list.size(); i++) {
            sheet.addCell(new Label(0, i + 1, list.get(i).getPoint()));
            sheet.addCell(new Label(1, i + 1, list.get(i).getPointName()));
            sheet.addCell(new Label(2, i + 1, list.get(i).getAddress()));
            sheet.addCell(new Label(3, i + 1, list.get(i).getAddressDetails()));
            sheet.addCell(new Label(4, i + 1, list.get(i).getThirdIndex()));
            sheet.addCell(new Label(5, i + 1, list.get(i).getStandard()));
            sheet.addCell(new Label(6, i + 1, list.get(i).getCode()));
            sheet.addCell(new Label(7, i + 1, list.get(i).getInvestigationStandard()));
            sheet.addCell(new Label(8, i + 1, list.get(i).getTestWay()));
            sheet.addCell(new Label(9, i + 1, list.get(i).getTestItem()));
            sheet.addCell(new Label(10, i + 1, list.get(i).getTestResult()));
            sheet.addCell(new Label(11, i + 1, list.get(i).getRecord()));
            sheet.addCell(new Label(12, i + 1, list.get(i).getPicture()));
        }
        workbook.write();
        workbook.close();
        return true;
    }
}
